/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package eyrolles;

import exception.DataAccessException;
import exception.ServiceException;
import exception.UserLoginException;
import exception.UserNotFoundException;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Properties;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

/**
 * Modele de l'application Eyrolles
 *
 * @author Nairo
 */
public class EyrollesModeleImpl implements EyrollesModele {

    private Properties properties;

    public void ajouterEmploye(Employe employe, String password)
            throws ServiceException, DataAccessException {

        StringBuffer sqlString =
                new StringBuffer("insert into employes ");

        sqlString.append("values ('"
                + employe.getUsername() + "', ");
        sqlString.append("'"
                + password + "', ");
        sqlString.append("'"
                + employe.getName() + "', ");
        sqlString.append(
                employe.getRoleid() + ", ");
        sqlString.append("'"
                + employe.getPhone() + "', ");
        sqlString.append("'"
                + employe.getEmail() + "', ");
        sqlString.append(
                employe.getDepid() + ")");

        Statement stmt = null;
        Connection conn = null;

        try {
            DataSource ds = getDataSource("datasource");
            conn = ds.getConnection();
            stmt = conn.createStatement();
        } catch (NamingException ne) {
            throw new DataAccessException(ne.getMessage());
        } catch (SQLException se) {
            throw new DataAccessException(se.getMessage());
        } finally {
            try {
                stmt.execute(sqlString.toString());
            } catch (SQLException se) {
                throw new ServiceException(se.getMessage());
            } finally {
                try {
                    if (stmt != null) {
                        stmt.close();
                    }
                } catch (SQLException se) {
                    throw new DataAccessException(se.getMessage());
                } finally {
                    try {
                        if (conn != null) {
                            conn.close();
                        }
                    } catch (SQLException se) {
                        throw new DataAccessException(se.getMessage());
                    }
                }
            }
        }
    }

    public void supprimerEmploye(String username) throws ServiceException, DataAccessException {

        StringBuffer sqlString =
                new StringBuffer("delete from employes ");
        sqlString.append("where username='" + username + "'");

        Connection conn = null;
        Statement stmt = null;

        try {
            DataSource ds = getDataSource("datasource");
            conn = ds.getConnection();
            stmt = conn.createStatement();
            stmt.execute(sqlString.toString());
        } catch (NamingException ne) {
            throw new DataAccessException(ne.getMessage());
        } catch (SQLException se) {
            throw new DataAccessException(se.getMessage());
        } finally {
            try {
                stmt.execute(sqlString.toString());
            } catch (SQLException se) {
                throw new ServiceException(se.getMessage());
            } finally {
                try {
                    if (stmt != null) {
                        stmt.close();
                    }
                } catch (SQLException se) {
                    throw new DataAccessException(se.getMessage());
                } finally {
                    try {
                        if (conn != null) {
                            conn.close();
                        }
                    } catch (SQLException se) {
                        throw new DataAccessException(se.getMessage());
                    }
                }
            }
        }
    }

    public void modifierEmploye(Employe employe, String password)
            throws ServiceException, DataAccessException {
        StringBuffer sqlString =
                new StringBuffer("update employes ");
        sqlString.append("set password='"
                + password + "', ");
        sqlString.append("roleid="
                + employe.getRoleid() + ", ");
        sqlString.append("name='"
                + employe.getName() + "', ");
        sqlString.append("phone='"
                + employe.getPhone() + "', ");
        sqlString.append("email='"
                + employe.getEmail() + "', ");
        sqlString.append("depid="
                + employe.getDepid());
        sqlString.append(" where username='"
                + employe.getUsername() + "'");
        Connection conn = null;
        Statement stmt = null;
        try {
            DataSource ds = getDataSource("datasource");
            conn = ds.getConnection();
            stmt = conn.createStatement();
        } catch (NamingException ne) {
            throw new DataAccessException(ne.getMessage());
        } catch (SQLException se) {
            throw new DataAccessException(se.getMessage());
        } finally {
            try {
                stmt.execute(sqlString.toString());
            } catch (SQLException se) {
                throw new ServiceException(se.getMessage());
            } finally {
                try {
                    if (stmt != null) {
                        stmt.close();
                    }
                } catch (SQLException se) {
                    throw new DataAccessException(se.getMessage());
                } finally {
                    try {
                        if (conn != null) {
                            conn.close();
                        }
                    } catch (SQLException se) {
                        throw new DataAccessException(se.getMessage());
                    }
                }
            }
        }
    }

    public String connecterEmploye(String username, String password) throws
            ServiceException, DataAccessException, UserLoginException {
        String user = null;
        ResultSet rs = null;
        Connection conn = null;
        Statement stmt = null;

        try {
            DataSource ds = getDataSource("datasource");
            conn = ds.getConnection();
            stmt = conn.createStatement();
        } catch (NamingException ne) {
            throw new DataAccessException(ne.getMessage());
        } catch (SQLException se) {
            throw new DataAccessException(se.getMessage());
        } finally {
            try {
                rs = stmt.executeQuery("select * from employes where username=\'"
                        + username + "' "
                        + "and password='" + password + "'");
                if (rs.next()) {
                    user = rs.getString("username");
                } else {
                    throw new UserLoginException(username);
                }
            } catch (SQLException se) {
                throw new ServiceException(se.getMessage());
            } finally {
                try {
                    if (rs != null) {
                        rs.close();
                    }
                    if (stmt != null) {
                        stmt.close();
                    }
                } catch (SQLException se) {
                    throw new DataAccessException(se.getMessage());
                } finally {
                    try {
                        if (conn != null) {
                            conn.close();
                        }
                    } catch (SQLException se) {
                        throw new DataAccessException(se.getMessage());
                    }
                }
                return user;
            }
        }
    }

    public ArrayList recupererEmployes()
            throws ServiceException, DataAccessException {
        Employe employe = null;
        ArrayList employes = new ArrayList();
        ResultSet rs = null;
        Connection conn = null;
        Statement stmt = null;

        try {
            DataSource ds = getDataSource("datasource");
            conn = ds.getConnection();
            stmt = conn.createStatement();
        } catch (NamingException ne) {
            throw new DataAccessException(ne.getMessage());
        } catch (SQLException se) {
            throw new DataAccessException(se.getMessage());
        } finally {
            try {
                rs = stmt.executeQuery("select * from employes, roles, "
                        + "services where employes.roleid=roles.roleid "
                        + "and employes.depid=services.depid");

                while (rs.next()) {

                    employe = new Employe();

                    employe.setUsername(rs.getString("username"));
                    employe.setName(rs.getString("name"));
                    employe.setRolename(rs.getString("rolename"));
                    employe.setPhone(rs.getString("phone"));
                    employe.setEmail(rs.getString("email"));
                    employe.setRoleid(new Integer(rs.getInt("roleid")));
                    employe.setDepid(new Integer(rs.getInt("depid")));
                    employe.setDepartment(rs.getString("depname"));

                    employes.add(employe);

                }
            } catch (SQLException se) {
                throw new ServiceException(se.getMessage());
            } finally {
                try {
                    if (rs != null) {
                        rs.close();
                    }
                    if (stmt != null) {
                        stmt.close();
                    }
                } catch (SQLException se) {
                    throw new DataAccessException(se.getMessage());
                } finally {
                    try {
                        if (conn != null) {
                            conn.close();
                        }
                    } catch (SQLException se) {
                        throw new DataAccessException(se.getMessage());
                    }
                }
                return employes;
            }
        }
    }

    @Override
    public Employe recupererEmploye(String username)
            throws ServiceException, DataAccessException, UserNotFoundException {
        Employe employe = null;
        ResultSet rs = null;
        Connection conn = null;
        Statement stmt = null;

        try {
            DataSource ds = getDataSource("datasource");
            conn = ds.getConnection();
            stmt = conn.createStatement();

        } catch (NamingException ne) {
            throw new DataAccessException(ne.getMessage());
        } catch (SQLException se) {
            throw new DataAccessException(se.getMessage());
        } finally {
            try {
                rs = stmt.executeQuery("select * from employes where username=\'"
                        + username + "'");

                if (rs.next()) {
                    employe = new Employe();
                    employe.setUsername(rs.getString("username"));
                    employe.setDepid(rs.getInt("depid"));
                    employe.setRoleid(rs.getInt("roleid"));
                    employe.setName(rs.getString("name"));
                    employe.setPhone(rs.getString("phone"));
                    employe.setEmail(rs.getString("email"));
                } else {
                    throw new UserNotFoundException(username);
                }
            } catch (SQLException se) {
                throw new ServiceException(se.getMessage());
            } finally {
                try {
                    if (rs != null) {
                        rs.close();
                    }
                    if (stmt != null) {
                        stmt.close();
                    }
                } catch (SQLException se) {
                    throw new DataAccessException(se.getMessage());
                } finally {
                    try {
                        if (conn != null) {
                            conn.close();
                        }
                    } catch (SQLException se) {
                        throw new DataAccessException(se.getMessage());
                    }
                }
                return employe;
            }
        }
    }

    public String recupererPassword(String username)
            throws ServiceException, DataAccessException {
        ResultSet rs = null;
        String password = null;
        Connection conn = null;
        Statement stmt = null;

        try {
            DataSource ds = getDataSource("datasource");
            conn = ds.getConnection();
            stmt = conn.createStatement();

        } catch (NamingException ne) {
            throw new DataAccessException(ne.getMessage());
        } catch (SQLException se) {
            throw new DataAccessException(se.getMessage());
        } finally {
            try {
                rs = stmt.executeQuery("select password From employes where username=\'"
                        + username + "'");

                if (rs.next()) {
                    password = rs.getString("password");
                }
            } catch (SQLException se) {
                throw new ServiceException(se.getMessage());
            } finally {
                try {
                    if (rs != null) {
                        rs.close();
                    }
                    if (stmt != null) {
                        stmt.close();
                    }
                } catch (SQLException se) {
                    throw new DataAccessException(se.getMessage());
                } finally {
                    try {
                        if (conn != null) {
                            conn.close();
                        }
                    } catch (SQLException se) {
                        throw new DataAccessException(se.getMessage());
                    }
                }
                return password;
            }
        }
    }

    @Override
    public void setProperties(Properties prop) {
        this.properties = prop;
    }
    
    @Override
    public DataSource getDataSource(String dataSourceName) throws NamingException{
        Context initCtx = new InitialContext();
        return (DataSource) initCtx.lookup(properties.getProperty(dataSourceName));
    }
}
